<?php
/**
 * Created by PhpStorm.
 * User: lingyun
 * Date: 2018/5/26
 * Time: 11:35
 * Desc: 导出管理
 */
namespace app\kuguan\controller;
use controller\BasicAdmin;
use service\DataService;
use think\Db;
use PHPExcel;
use PHPExcel_IOFactory;

class Export extends BasicAdmin{
    /**
     * @Author  lingyun
     * @Desc    导出原料信息
     * 2018-05-26
     */
    public function export_material(){
        $get = input('get.');
        $map['is_deleted'] = ['eq',0];
        $map['store_id'] = ['eq',0];
        if(!empty($get['cate_1'])){
            $map['first_category'] = ['like',"%".$get['cate_1']."%"];
        }
        if(!empty($get['cate_2'])){
            $map['second_category'] = ['like',"%".$get['cate_2']."%"];
        }
        if(!empty($get['cate_3'])){
            $map['third_category'] = ['like',"%".$get['cate_3']."%"];
        }
        if(!empty($get['name'])){
            $map['name'] = ['like',"%".$get['name']."%"];
        }

        $data = db('goods_material')->where($map)->select();

        $filename = '原料详情'.date('YmdHis').'.xls';

        $objPHPExcel = new PHPExcel();//新建一个表
        $objSheet = $objPHPExcel->getActiveSheet();//获得当前活动Sheet
        $objSheet->setTitle("原料详情");
        $title = '原料详情 - '.date('Y-m-d',time());

        $objSheet->setCellValue('A1', $title);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');      //合并
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(     //加粗和居中
            array(
                'font' => array (
                    'bold' => true
                ),
                'alignment' => array(
                    'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                )
            )
        );


        $objSheet
            ->setCellValue('A2', '原料编码')
            ->setCellValue('B2', '条码')
            ->setCellValue('C2', '原料名称')
            ->setCellValue('D2', '标准售价')
            ->setCellValue('E2', '单位')
            ->setCellValue('F2', '库存')
            ->setCellValue('G2', '一级分类')
            ->setCellValue('H2', '二级分类')
            ->setCellValue('I2', '三级分类');


        $j = 3;     //从第三行开始

        foreach ($data as $key => $value) {
            if(empty($value['res_num'])){
                $value['res_num'] = '未处理';
            }
            if(empty($value['dif_num'])){
                $value['dif_num'] = '未处理';
            }
            $objSheet
                ->setCellValue('A' . $j, $value['code'])
                ->setCellValue('B' . $j, $value['tiaoma'])
                ->setCellValue('C' . $j, $value['name'])
                ->setCellValue('D' . $j, $value['price'])
                ->setCellValue('E' . $j, $value['unit'])
                ->setCellValue('F' . $j, $value['stock'])
                ->setCellValue('G' . $j, $value['first_category'])
                ->setCellValue('H' . $j, $value['second_category'])
                ->setCellValue('I' . $j, $value['third_category']);
            $j ++;
        }

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $objWriter->save("php://output");

    }

    /**
     * @Author  lingyun
     * @Desc    导出原料订单详情
     */
    public function export_material_order(){
        $get = input('get.');
        $map = [];
        $map['a.is_deleted'] = array('eq',0);
        $store_id = empty(input('store_id'))?0:input('store_id');
        $map['a.store_id'] = array('eq',$store_id);
        $join = [
            ['ys_system_user b','a.admin_id=b.id','LEFT'],
            ['ys_system_user c','a.receive_admin=c.id','LEFT'],
            ['ys_material_stock_log d','d.order_id=a.id','LEFT'],
            ['ys_goods_material e','e.id=d.material_id','LEFT']
        ];

        $join1 = [
            ['ys_system_user b','a.admin_id=b.id','LEFT'],
            ['ys_system_user c','a.receive_admin=c.id','LEFT']
        ];

        $join2 = [
            ['ys_material_stock_log d','d.order_id=a.id','LEFT'],
            ['ys_goods_material e','e.id=d.material_id','LEFT']
        ];

        if(!empty($get['yw_type'])){
            $map['a.yw_type'] = ['eq',$get['yw_type']];
        }

        if(!empty($get['gys'])){
            $map['a.gys'] = ['eq',$get['gys']];
        }

        if($get['status'] == 1){
            if(!empty($get['order_sn'])){
                $map['a.order_sn'] = array('eq',$get['order_sn']);

            }
            if(isset($get['date']) && $get['date'] !== '') {
                list($start, $end) = explode('-', str_replace(' ', '', $get['date']));
                $start = $start."  00:00:00";		$end = $end." 23:59:59";
                $start = strtotime($start);			$end = strtotime($end);
            }
        }else{
            if($get['status'] === 0){
                $map['a.status'] = ['eq',$get['status']];
            }
        }

        //订单列表
        $order_list = Db::name('material_order')->alias('a')->join($join1)->field('a.*,b.realname as create_admin,c.realname as receive_admin,(select count(*) from ys_material_stock_log where order_id=a.id) as count')->where($map)->order('a.id desc')->select();

        //库存详情记录
//        $stock_list = Db::name('material_order')->alias('a')->join($join2)->field('a.id,e.name,e.unit,d.del_num,d.res_num,d.dif_num,d.receive_signal_price,d.receive_price')->where($map)->order('a.id desc')->select();

//        $list = Db::name('material_order')->alias('a')->join($join)->field('a.*,b.realname as create_admin,c.realname as receive_admin,e.name,e.unit,d.del_num,d.res_num,d.dif_num,d.receive_price')->where($map)->order('a.id desc')->select();

        $filename = '原料订单'.date('YmdHis').'.xls';

        $objPHPExcel = new PHPExcel();//新建一个表
        $objSheet = $objPHPExcel->getActiveSheet();//获得当前活动Sheet
        $objSheet->setTitle("原料订单");
        $title = '原料订单 - '.date('Y-m-d',time());

        $objSheet->setCellValue('A1', $title);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');      //合并
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(     //加粗和居中
            array(
                'font' => array (
                    'bold' => true
                ),
                'alignment' => array(
                    'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                )
            )
        );


        $objSheet
            ->setCellValue('A2', '订单号')
            ->setCellValue('B2', '创建时间')
            ->setCellValue('C2', '业务类型')
            ->setCellValue('D2', '供应商')
            ->setCellValue('E2', '制单人')
            ->setCellValue('F2', '审核人')
            ->setCellValue('G2', '原料名称')
            ->setCellValue('H2', '单位')
            ->setCellValue('I2', '实收数量')
            ->setCellValue('J2', '单价')
            ->setCellValue('K2', '金额');

        $j = 3;     //从第三行开始

        foreach ($order_list as $key => $value) {         //循环导出数据

            $log = db('material_stock_log')->alias('a')->join('ys_goods_material b','a.material_id=b.id','LEFT')->where('a.order_id',$value['id'])->field('a.*,b.name,b.unit')->select();          //原来订单详情记录

            foreach($log as $k => $v){
                if($k == 0){
//                    if($value['count'] == 1){           //只有一个商品申请信息
                        if($value['yw_type'] == 1){         //
                            $type = '普通采购';
                        }else if($value['yw_type'] == 2){       //
                            $type = '采购退货';
                        }else{
                            $type = '生产使用';
                        }

                        if($v['receive_signal_price'] == ''){
                            $receive_signal_price = '暂未对账';
                        }else{
                            $receive_signal_price = $v['receive_signal_price'];
                        }

                        if($v['receive_price'] == ''){
                            $receive_price = '暂未对账';
                        }else{
                            $receive_price = $v['receive_price'];
                        }

                        $objSheet
                            ->setCellValue('A' . $j, $value['order_sn'])
                            ->setCellValue('B' . $j, date('Y-m-d H:i',$value['addtime']))
                            ->setCellValue('C' . $j, $type)
                            ->setCellValue('D' . $j, $value['gys'])
                            ->setCellValue('E' . $j, $value['create_admin'])
                            ->setCellValue('F' . $j, $value['receive_admin'])
                            ->setCellValue('G' . $j, $v['name'])
                            ->setCellValue('H' . $j, $v['unit'])
                            ->setCellValue('I' . $j, $v['res_num'])
                            ->setCellValue('J' . $j, $receive_signal_price)
                            ->setCellValue('K' . $j, $receive_price);
                        $j ++;
//                    }
                }else{
                    if(!empty($log[$k])){
                        if($v['receive_signal_price'] == ''){
                            $receive_signal_price = '暂未对账';
                        }else{
                            $receive_signal_price = $v['receive_signal_price'];
                        }

                        if($v['receive_price'] == ''){
                            $receive_price = '暂未对账';
                        }else{
                            $receive_price = $v['receive_price'];
                        }


                        $objSheet
                            ->setCellValue('A' . $j, '')
                            ->setCellValue('B' . $j, '')
                            ->setCellValue('C' . $j, '')
                            ->setCellValue('D' . $j, '')
                            ->setCellValue('E' . $j, '')
                            ->setCellValue('F' . $j, '')
                            ->setCellValue('G' . $j, $v['name'])
                            ->setCellValue('H' . $j, $v['unit'])
                            ->setCellValue('I' . $j, $v['res_num'])
                            ->setCellValue('J' . $j, $receive_signal_price)
                            ->setCellValue('K' . $j, $receive_price);
                        $j ++;
                    }
                }
            }

        }

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $objWriter->save("php://output");

    }

    /**
     * @Author  lingyun
     * @Desc    导出单个订单
     * 2018-07-23
     */
    public function export_signal_order(){
        $get = input('get.');
        $map = [];
        $map['a.is_deleted'] = array('eq',0);
        $map['a.id'] = ['eq',$get['order_id']];
        $join = [
            ['ys_system_user b','a.admin_id=b.id','LEFT'],
            ['ys_system_user c','a.receive_admin=c.id','LEFT'],
            ['ys_material_stock_log d','d.order_id=a.id','LEFT'],
            ['ys_goods_material e','e.id=d.material_id','LEFT']
        ];

        $join1 = [
            ['ys_system_user b','a.admin_id=b.id','LEFT'],
            ['ys_system_user c','a.receive_admin=c.id','LEFT']
        ];

        $join2 = [
            ['ys_material_stock_log d','d.order_id=a.id','LEFT'],
            ['ys_goods_material e','e.id=d.material_id','LEFT']
        ];


        //订单列表
        $order_list = Db::name('material_order')->alias('a')->join($join1)->field('a.*,b.realname as create_admin,c.realname as receive_admin,(select count(*) from ys_material_stock_log where order_id=a.id) as count')->where($map)->order('a.id desc')->find();

        $filename = '原料订单'.date('YmdHis').'.xls';

        $objPHPExcel = new PHPExcel();//新建一个表
        $objSheet = $objPHPExcel->getActiveSheet();//获得当前活动Sheet
        $objSheet->setTitle("原料订单");
        $title = '原料订单 - '.$order_list['order_sn'];

        $objSheet->setCellValue('A1', $title);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');      //合并
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(     //加粗和居中
            array(
                'font' => array (
                    'bold' => true
                ),
                'alignment' => array(
                    'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                )
            )
        );


        $objSheet
            ->setCellValue('A2', '业务类型')
            ->setCellValue('B2', '供应商')
            ->setCellValue('C2', '制单人')
            ->setCellValue('D2', '审核人')
            ->setCellValue('E2', '原料名称')
            ->setCellValue('F2', '单位')
            ->setCellValue('G2', '申请数量')
            ->setCellValue('H2', '实收数量')
            ->setCellValue('I2', '单价')
            ->setCellValue('J2', '金额');

        $j = 3;     //从第三行开始

        $log = db('material_stock_log')->alias('a')->join('ys_goods_material b','a.material_id=b.id','LEFT')->where('a.order_id',$order_list['id'])->field('a.*,b.name,b.unit')->select();          //原来订单详情记录

        foreach($log as $k => $v){
            if($k == 0){
//                    if($value['count'] == 1){           //只有一个商品申请信息
                if($order_list['yw_type'] == 1){         //
                    $type = '普通采购';
                }else if($order_list['yw_type'] == 2){       //
                    $tyep = '采购退货';
                }else{
                    $type = '生产使用';
                }

                if($v['receive_signal_price'] == ''){
                    $receive_signal_price = '暂未对账';
                }else{
                    $receive_signal_price = $v['receive_signal_price'];
                }

                if($v['receive_price'] == ''){
                    $receive_price = '暂未对账';
                }else{
                    $receive_price = $v['receive_price'];
                }

                $objSheet
                    ->setCellValue('A' . $j, $type)
                    ->setCellValue('B' . $j, $order_list['gys'])
                    ->setCellValue('C' . $j, $order_list['create_admin'])
                    ->setCellValue('D' . $j, $order_list['receive_admin'])
                    ->setCellValue('E' . $j, $v['name'])
                    ->setCellValue('F' . $j, $v['unit'])
                    ->setCellValue('G' . $j, $v['del_num'])
                    ->setCellValue('H' . $j, $v['res_num'])
                    ->setCellValue('I' . $j, $receive_signal_price)
                    ->setCellValue('J' . $j, $receive_price);
                $j ++;
//                    }
            }else{
                if(!empty($log[$k])){
                    if($v['receive_signal_price'] == ''){
                        $receive_signal_price = '暂未对账';
                    }else{
                        $receive_signal_price = $v['receive_signal_price'];
                    }

                    if($v['receive_price'] == ''){
                        $receive_price = '暂未对账';
                    }else{
                        $receive_price = $v['receive_price'];
                    }


                    $objSheet
                        ->setCellValue('A' . $j, $type)
                        ->setCellValue('B' . $j, $order_list['gys'])
                        ->setCellValue('C' . $j, $order_list['create_admin'])
                        ->setCellValue('D' . $j, $order_list['receive_admin'])
                        ->setCellValue('E' . $j, $v['name'])
                        ->setCellValue('F' . $j, $v['unit'])
                        ->setCellValue('G' . $j, $v['del_num'])
                        ->setCellValue('H' . $j, $v['res_num'])
                        ->setCellValue('I' . $j, $receive_signal_price)
                        ->setCellValue('J' . $j, $receive_price);
                    $j ++;
                }
            }
        }

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $objWriter->save("php://output");
    }


}